﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utils;
using DataHelper;
//员工的基本工资项
namespace DAL
{
    public class EmpItemDao
    {

        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().UpdateTable(tableName, sdList);
        }
        public bool Insert(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().InsertTable(tableName, sdList);
        }
        public DataTable GetOneInfo(string empCode)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from View_EmpItem where EmpCode='" + empCode + "'");
        }
        //获取某个员工某个工资项的值
        public string GetWageItemValue(string empCode,string wageItem )
        {
            return SQLHelper.ExecuteSqlGetNumber("Select " + wageItem + " from View_EmpItem where EmpCode='" + empCode + "'");
        }
        public DataTable GetOneInfo(string empCode, string setsID)
        {
            return SQLHelper.ExecuteSqlGetDataSet("Select * from View_EmpItem where EmpCode='" + empCode + "' and SetsID=" + setsID).Tables[0];
        }

        public bool DeleteOneInfo(string empCode)
        {
            return SQLHelper.ExecuteSql("delete from Wage_EmpItem where EmpCode='" + empCode + "'");
        }

        public bool IsExistEmpCode(string empCode)
        {
            DataTable dt = GetOneInfo(empCode);
            if (dt.Rows.Count == 1)//如果存在该用户编号
                return true;
            else//否则
                return false;
        }

        public bool IsExistEmpCode(string empCode,string setsID)
        {
            DataTable dt = GetOneInfo(empCode, setsID);
            if (dt.Rows.Count == 1)//如果存在该用户编号
                return true;
            else//否则
                return false;
        }
        //更新一列
        //修改帐套的项目时使用
        public bool UpdateAllInfo(string itemCode, string initValue, string setsID)
        {
            string strSQL = "UPDATE t1 SET " + itemCode + "=" + initValue + "  FROM Wage_EmpItem t1 , Wage_EmpSets t2 WHERE (t2.SetsID=" + setsID + " AND  t2.EmpCode = t1.EmpCode)";
            return SQLHelper.ExecuteSql(strSQL);
        }
        public DataTable GetLists(string empCode, string deptName, string setsID)
        {
            //直接确定要显示的列
           // StringBuilder fields = new StringBuilder(" DeptName as 部门名称,EmpCode as 工号,EmpName as 员工姓名,DutyName as 职务");
           // Hashtable ht = new SetsItemDao().GetFieldsHashTable(setsID);
            //合成字段
           // ArrayList akeys = new ArrayList(ht.Keys);
           // akeys.Sort();
            //foreach (object skey in akeys)
            //{
            //    fields.Append("," + skey + " as " + ht[skey]);
           // }           

            string showFields = " DeptName as 部门名称,EmpCode as 工号,BankAccount as 银行账号,EmpName as 员工姓名,DutyName as 职务" + new SetsItemDao().GetFieldsDic(setsID);
            if (!string.IsNullOrWhiteSpace(empCode))
            {
                return SQLHelper.ExecuteSqlGetDrToDt("Select " + showFields + " from View_EmpItem where EmpCode='" + empCode + "' Order by DeptName ASC,EmpCode ASC");
            }
            else if (deptName != "请选择部门")
            {
                return SQLHelper.ExecuteSqlGetDrToDt("Select  " + showFields + " from View_EmpItem where DeptName='" + deptName + "'  Order by DeptName ASC,EmpCode ASC");
            }
            else
                return SQLHelper.ExecuteSqlGetDrToDt("Select  " + showFields + " from View_EmpItem where SetsID=" + setsID + " Order by DeptName ASC,EmpCode ASC");
        }
        public DataTable GetOutList(string empCode, string deptName, string setsID)
        {
            string strTable = " View_EmpItem ";
            string itemFields = new SetsItemDao().GetFieldsDic(setsID);
            string mainField = "DeptName as 部门,EmpCode as 工号,EmpName as 姓名,DutyName as 职务,EmpSex as 性别,ZipCode as 身份证 ";
           

            StringBuilder strSQL = new StringBuilder();
            if (!string.IsNullOrWhiteSpace(empCode))
                strSQL.Append("select " + mainField + itemFields+" from  " + strTable + " where SetsID=" + setsID + " and  (EmpCode='" + empCode + "' or EmpName like '%" + empCode + "%') ");
            else if (deptName != "请选择部门")
                strSQL.Append("select " + mainField + itemFields + " from  " + strTable + " where SetsID=" + setsID + " and  DeptName='" + deptName + "' ");
            else   
                strSQL.Append("select " + mainField + itemFields + " from  " + strTable + " where  SetsID=" + setsID);

            strSQL.Append(" order by DeptName ASC,EmpCode ASC");
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }
        //无基本工资项的员工
        public DataTable GetLists()
        {
            string strSQL = "select * from View_EmpSets where EmpCode not in (select EmpCode from Wage_EmpItem)  Order by DeptID ASC,EmpCode ASC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //无银行账号员工
        public DataTable GetNoLists()
        {
            string strSQL = "select * from View_EmpItem where  isnull(BankAccount,'')='' Order by DeptName ASC,EmpCode ASC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //为所有 具有选定帐套，而没有基本工资项 的员工， 一次根据默认全部设置
        public bool AddAllEmpCode(string setsID)
        {
            return false;
            //因为要设置银行账号，小时工资，工资等级
            //还是要一个一个设置，所以不提供此功能
        }
    }
}
